package com.bdth.bdthiss.plus;

import cn.hutool.core.io.FileUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.baomidou.mybatisplus.generator.config.DataSourceConfig;
import com.baomidou.mybatisplus.generator.config.rules.DbType;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

/**
 * Created by Administrator on 2020/4/21.
 */
public class JdItemGe {

    public static void main(String[] args) {
        ExcelReader reader = ExcelUtil.getReader(FileUtil.file("C:\\1212.xlsx"));
        List<List<Object>> read = reader.read(2, reader.getRowCount());
        for (List<Object> objects : read) {
            String typename=(String) objects.get(0);
            String subtypename=(String) objects.get(1);
            Object objid = objects.get(3);
            String skuid=objid.toString();
            String goodsurl=(String) objects.get(9);
            Object goodsObj = objects.get(5);
            String goodsname ="";
            if(goodsObj!=null)
                goodsname=(String) objects.get(5);
            Object objprice = objects.get(7);
            Object objsellprice = objects.get(6);
            String brand=(String) objects.get(13);
            double price = Double.parseDouble(objprice.toString());
            double sellprice = Double.parseDouble(objsellprice.toString());
            String sql = String.format("insert into bdth_jdgoods(typename,subtypename,skuid,goodsurl,goodsname,price,sellprice,brand) " +
                    "values('%s','%s','%s','%s','%s',%f,%f,'%s')",typename,subtypename,skuid,goodsurl,goodsname,price,sellprice,brand);
            System.out.println(sql+";");
            //saveGoods(sql);
        }

    }

    /**
     * 获取数据库连接
     * @param database
     * @return
     */
    public static Connection getConnection(String database){
        String dbUrl = "jdbc:mysql://39.105.26.214:3306/" + database + "?useUnicode=true&characterEncoding=utf8&autoReconnect=true&useSSL=false";
        DataSourceConfig dataSourceConfig = new DataSourceConfig();
        dataSourceConfig.setDbType(DbType.MYSQL)
                .setUrl(dbUrl)
                .setUsername("bdth")
                .setPassword("bdth1234")
                .setDriverName("com.mysql.jdbc.Driver");
        return dataSourceConfig.getConn();
    }

    public static void saveGoods(String sql){
        Connection connection = JdItemGe.getConnection("bdth_mall_data");
        PreparedStatement ps = null;
        try{
            ps = connection.prepareStatement(sql);
            ps.execute();
        }catch (SQLException e){
            System.out.println(e.getMessage());
        }finally {
            try{
                if(ps!=null){
                    ps.close();
                }
                if(connection!=null)
                    connection.close();
            }catch (SQLException e){
                System.out.println(e.getMessage());
            }
        }
    }
}
